Outdated statistics are a major cause of bad query plans in SQL Server’s.
Even when we have ‘Auto Update Statistics’ on, this doesn’t solve the problem, because the formula for the number of modifications that triggers the ‘Auto Update Statistics’ is 20% + 500. If your table has 3 million rows you will need to wait until 1.8 million + 500 modifications.
Configuring trace flag 2371, created in service pack 1 of SQL Server 2008 R2 helps a lot, so the formula became dynamic. When we achieve 1 million rows, for example, the formula will be 10% + 500 and so on.
But this doesn’t solve the problem, between zero and 100 thousands modifications our server can face a lot of wrong query plans, so we still need to check the statistics and manually update them.
In SQL Server 2012 the DMF sys.dm_db_stats_properties was created. The best about this function is that it allows to us not only retrieve the last update date for each stats, but also allow us to retrieve the number of modifications already happened since the last update, so we can identify if the statistics is updated or is needing to be manually updated.
Here is a query to achieve this :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT sch.name as [schema], o.name AS "Table", c.name, [s].[name] AS "Statistic", [sp].[last_updated] AS "Last Updated", [sp].[rows], [sp].[rows_sampled], [sp].[modification_counter] AS "Modifications" FROM [sys].[stats] AS [s] inner join sys.stats_columns sc on s.stats_id=sc.stats_id and s.object_id=sc.object_id inner join sys.columns c on c.object_id=sc.object_id and c.column_id=sc.column_id inner join sys.objects o on s.object_id=o.object_id inner join sys.schemas sch on o.schema_id=sch.schema_id OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp] WHERE [s].[object_id] = OBJECT_ID(N'Production.Product'); |
This is one more excellent query to become a function in our DBA toolset, below you can see how the function will be:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
-- ================================================================== -- Author: Denes Torres -- Create date: 19/04/2015 -- Description: This function retrieves information about statistics -- such as last update and number of modifications -- ================================================================== CREATE FUNCTION StatisticsInfo() RETURNS TABLE AS RETURN ( -- Add the SELECT statement with parameter references here SELECT sch.name as [schema], o.name AS "Table", c.name, [s].[name] AS "Statistic", [sp].[last_updated] AS "Last Updated", [sp].[rows], [sp].[rows_sampled], [sp].[modification_counter] AS "Modifications" FROM [sys].[stats] AS [s] inner join sys.stats_columns sc on s.stats_id=sc.stats_id and s.object_id=sc.object_id inner join sys.columns c on c.object_id=sc.object_id and c.column_id=sc.column_id inner join sys.objects o on s.object_id=o.object_id inner join sys.schemas sch on o.schema_id=sch.schema_id OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp] ) GO |
After creating this function we can check the statistics for a table with a simple query:
|
1 2 |
select * from dbo.StatisticsInfo() where Table='Product' and Schema='Production' |
In the result we can find how many modifications already happened since last update, identifying if the statistics needs to be manually updated
Load comments